﻿using System.Text;
using BaseData.CommonLib.Models;
using Dapper;
using Microsoft.Data.SqlClient;
using Swimj.CommonLib.Base;
using Swimj.DataCore;

namespace BaseData.ApplicationLib.Queries
{
    public class MaterialCategoryQueries : IMaterialCategoryQueries
    {
        private readonly string? _defaultConnStr;

        public MaterialCategoryQueries(string? defaultConnStr)
        {
            _defaultConnStr = defaultConnStr;
        }

        public async Task<Result<IList<MaterialCategoryTreeResponseDto>>?> QueryMaterialCategoriesByTree()
        {
            IEnumerable<MaterialCategoryInfoResponseDto>? data = null;
            using (var db = new SqlConnection(_defaultConnStr))
            {
                data = await db.QueryAsync<MaterialCategoryInfoResponseDto>(@"
select id,
       code,
       name,
       parentId,
       sort,
       description,
       datastate,
       createdby,
       createdon,
       lastmodifiedby,
       lastmodifiedon,
       deletedby,
       deletedon
from MES_MATERIAL_CATEGORY where 1 = 1 ");
            }

            IList<MaterialCategoryTreeResponseDto> treeDtos = new List<MaterialCategoryTreeResponseDto>();
            // 获取菜单目录
            var rootMenus = data.Where(i => i.ParentId == null || i.ParentId == 0)
                .ToList();
            if (rootMenus.Count > 0)
            {
                foreach (var item in rootMenus)
                {
                    treeDtos.Add(GetChildrens(item, data.ToList()));
                }
            }

            return await Result<IList<MaterialCategoryTreeResponseDto>>.SuccessAsync(treeDtos);
        }

        private MaterialCategoryTreeResponseDto GetChildrens(MaterialCategoryInfoResponseDto parent,
            IList<MaterialCategoryInfoResponseDto> menus)
        {
            var treeDto = new MaterialCategoryTreeResponseDto
            {
                Key = parent.Id,
                Code = parent.Code,
                Name = parent.Name
            };

            var childCategories = menus.Where(i => i.ParentId == parent.Id)
                .ToList();
            if (childCategories.Count > 0)
            {
                treeDto.Children = new List<MaterialCategoryTreeResponseDto>();
                foreach (var child in childCategories)
                {
                    treeDto.Children.Add(GetChildrens(child, menus));
                }
            }

            return treeDto;
        }

        public async Task<Result<MaterialCategoryInfoResponseDto>?> GetMaterialCategory(int id)
        {
            MaterialCategoryInfoResponseDto materialCategory = null;
            using (var db = new SqlConnection(_defaultConnStr))
            {
                materialCategory = db.QueryFirstOrDefault<MaterialCategoryInfoResponseDto>(@"
select id ,
       code,
       name,
       parentId,
       sort,
       description,
       datastate,
       createdby,
       createdon,
       lastmodifiedby,
       lastmodifiedon,
       deletedby,
       deletedon
from MES_MATERIAL_CATEGORY where 1 = 1 and id = @key
             ", new { key = id });
            }

            return await Result<MaterialCategoryInfoResponseDto>.SuccessAsync(materialCategory);
        }

        public async Task<PaginatedResult<MaterialCategoryInfoResponseDto>?> QueryMaterialCategoriesByPage(
            MaterialCategorySearchRequestDto model)
        {
            IEnumerable<MaterialCategoryInfoResponseDto>? data = null;
            int totalCount = 0;
            using (var db = new SqlConnection(_defaultConnStr))
            {
                var query = new Query(db);

                StringBuilder sqlBuilder = new StringBuilder(@"select id ,
            code,
            name,
            parentId,
            sort,
            description,
            datastate,
            createdby,
            createdon,
            lastmodifiedby,
            lastmodifiedon,
            deletedby,
            deletedon
                from MES_MATERIAL_CATEGORY where 1 = 1 ");

                DynamicParameters parameters = new DynamicParameters();

                if (model.ParentId != null || model.ParentId > 0)
                {
                    sqlBuilder.Append(" and parentId = @parentId");
                    parameters.Add("parentId", model.ParentId);
                }
                else
                {
                    sqlBuilder.Append(" and parentId is null");
                }

                query.BuildPageQueries(model.Page, model.Limit, sqlBuilder.ToString());

                data = await db.QueryAsync<MaterialCategoryInfoResponseDto>(query.SqlPage, parameters);

                totalCount = await db.ExecuteScalarAsync<int>(query.SqCount, parameters);
            }

            return await PaginatedResult<MaterialCategoryInfoResponseDto>.SuccessAsync(data.ToList(), totalCount,
                model.Page,
                model.Limit);
        }


        public async Task<Result<IList<MaterialCategoryInfoResponseDto>>?> QueryMaterialCategories(
            MaterialCategorySearchRequestDto model)
        {
            IEnumerable<MaterialCategoryInfoResponseDto>? data = null;
            using (var db = new SqlConnection(_defaultConnStr))
            {
                data = await db.QueryAsync<MaterialCategoryInfoResponseDto>(@"
select id ,
       code,
       name,
       parentId,
       sort,
       description,
       datastate,
       createdby,
       createdon,
       lastmodifiedby,
       lastmodifiedon,
       deletedby,
       deletedon
from MES_MATERIAL_CATEGORY where 1 = 1 ");
            }

            return await Result<IList<MaterialCategoryInfoResponseDto>>.SuccessAsync(data.ToList());
        }
    }
}